package com.leenmvc.core.utils;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.hutool.core.date.DateException;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.leenmvc.core.annotation.ExcelColumn;
import com.leenmvc.core.exception.BusinessException;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.BeanUtils;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.*;

/**
 * excel工具类
 *
 * @author Mark HongJL,HuangZG
 */
public class ExcelUtils {

    /**
     * Excel导出
     *
     * @param response  response
     * @param fileName  文件名
     * @param list      数据List
     * @param pojoClass 对象Class
     */
    public static void exportExcel(HttpServletResponse response, String fileName, Collection<?> list,
                                   Class<?> pojoClass) throws IOException {
        if (StringUtils.isBlank(fileName)) {
            //当前日期
            fileName = DateUtils.format(new Date());
        }
        ExportParams exportParams = new ExportParams();
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        // 自适应单元格宽度实现  排除两个类
        Sheet sheet = workbook.getSheetAt(0);
        int maxColumn = sheet.getRow(0).getPhysicalNumberOfCells();
        for (int i = 0; i < maxColumn; i++) {
            sheet.autoSizeColumn(i);
        }
        for (int columnNum = 0; columnNum <= maxColumn; columnNum++) {
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row currentRow;
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }

                if (currentRow.getCell(columnNum) != null) {
                    Cell currentCell = currentRow.getCell(columnNum);
                    try {
                        int length = currentCell.toString().getBytes("GBK").length;
                        if (columnWidth < length + 1) {
                            columnWidth = length + 8;
                        }
                    } catch (UnsupportedEncodingException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                }
            }
            if (columnWidth > 255) {
                columnWidth = 255;
            }
            sheet.setColumnWidth(columnNum, columnWidth * 256);
        }
        response.setCharacterEncoding("UTF-8");
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition",
                "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
        ServletOutputStream out = response.getOutputStream();
        workbook.write(out);
        out.flush();
    }

    /**
     * Excel导出，先sourceList转换成List<targetClass>，再导出
     *
     * @param response    response
     * @param fileName    文件名
     * @param sourceList  原数据List
     * @param targetClass 目标对象Class
     */
    public static void exportExcelToTarget(HttpServletResponse response, String fileName, Collection<?> sourceList,
                                           Class<?> targetClass) {
        try {
            List targetList = new ArrayList<>(sourceList.size());
            for (Object source : sourceList) {
                Object target = targetClass.newInstance();
                BeanUtils.copyProperties(source, target);
                targetList.add(target);
            }
            exportExcel(response, fileName, targetList, targetClass);
        } catch (Exception e) {
            throw new BusinessException("");
        }
    }

    /**
     * 将sheet转成一个对象列表
     *
     * @param cls
     * @return
     */
    public static <T> List<T> covertTolist(InputStream inputStream, int whichSheet, Class<?> cls) throws IllegalAccessException, InstantiationException, NoSuchMethodException, InvocationTargetException, FileNotFoundException {
        ExcelReader reader = ExcelUtil.getReader(inputStream);
        Workbook workbook = reader.getWorkbook();
        Sheet sheetAt = workbook.getSheetAt(whichSheet);
        List<T> list = new ArrayList<>();
        Map<Integer, Field> columnValues = new HashMap<Integer, Field>();
        for (Row row : sheetAt) {
            short lastCellNum = row.getLastCellNum();
            if (row.getRowNum() == 0) { // 定义下列表中的中文分别对应的对象中的字段关系
                for (int i = 0; i < lastCellNum; i++) {
                    row.getCell(i).setCellType(CellType.STRING);
                    columnValues.put(i, getFieldName(cls, row.getCell(i).getStringCellValue()));
                }
                continue;
            }
            Object obj = cls.newInstance();
            boolean flag = true;
            for (int i = 0; i < lastCellNum; i++) {
                Cell cell = row.getCell(i);
                if (cell == null) {
                    flag = false;
                    continue;
                }
                if (StringUtils.isEmpty(String.valueOf(cell))) {
                    flag = false;
                    continue;
                }
                flag = true;
                Field field = columnValues.get(i);
                String name = field.getName();
                Method method = cls.getDeclaredMethod("set" + upperCase(name), field.getType());
                setValue(obj, method, cell, field);
            }
            if (flag) list.add((T) obj);
        }
        return list;
    }

    /**
     * @param obj
     * @param method
     * @param cell
     * @param field
     * @throws InvocationTargetException
     * @throws IllegalAccessException
     */
    private static void setValue(Object obj, Method method, Cell cell, Field field) throws InvocationTargetException, IllegalAccessException, DateException {
        String type = field.getType().getSimpleName();
        switch (type) {
            case "String":
                cell.setCellType(CellType.STRING);
                String cellValue = cell.getStringCellValue();
                method.invoke(obj, cellValue);
                break;
            case "Short":
            case "short":
                cell.setCellType(CellType.STRING);
                String numericCellValue = cell.getStringCellValue();
                method.invoke(obj, new Short(numericCellValue));
                break;
            case "Byte":
            case "byte":
                cell.setCellType(CellType.STRING);
                numericCellValue = cell.getStringCellValue();
                method.invoke(obj, new Byte(numericCellValue));
                break;
            case "int":
            case "Integer":
                cell.setCellType(CellType.STRING);
                numericCellValue = cell.getStringCellValue();
                method.invoke(obj, new Integer(numericCellValue));
                break;
            case "Float":
            case "float":
                cell.setCellType(CellType.STRING);
                numericCellValue = cell.getStringCellValue();
                method.invoke(obj, new Float(numericCellValue));
                break;
            case "Double":
            case "double":
                cell.setCellType(CellType.STRING);
                numericCellValue = cell.getStringCellValue();
                method.invoke(obj, new Double(numericCellValue));
                break;
            case "Date":
                cell.setCellType(CellType.STRING);
                cellValue = cell.getStringCellValue();
                method.invoke(obj, cellValue);
                break;
        }
    }

    /**
     * 首字母大写
     *
     * @param str
     * @return
     */
    private static String upperCase(String str) {
        char[] ch = str.toCharArray();
        if (ch[0] >= 'a' && ch[0] <= 'z') {
            ch[0] = (char) (ch[0] - 32);
        }
        return new String(ch);
    }

    /**
     * @param cls
     * @param comment
     * @return
     */
    private static Field getFieldName(Class<?> cls, String comment) {
        Field[] fields = cls.getDeclaredFields();
        for (Field field : fields) {
            ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
            if (annotation != null) {
                String value = annotation.value();
                if (value.equals(comment)) {
                    return field;
                }
            }

        }
        return null;
    }
}
